package com.dragon.repository.mapper;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.dragon.repository.config.annotation.DataScope;
import com.dragon.repository.entity.Payment;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.LinkedHashMap;
import java.util.Map;

/**
 * <p>
 * 付款 Mapper 接口
 * </p>
 *
 * @author sy
 * @since 2023-11-08
 */
@Mapper
public interface PaymentMapper extends BaseMapper<Payment> {

    @Select("<script>" +
            "select M.* from (select a.*,aa.username as adminName, ad.name as departmentName from dragon_payment a  " +
            "LEFT JOIN dragon_admin aa on a.admin_id  = aa.id " +
            "LEFT JOIN dragon_department ad on a.department_id = ad.id) M " +
            "${ew.customSqlSegment} " +
            "</script>")
    @DataScope( adminAlias = "M" ,adminField = "admin_id", deptField = "department_id", deptAlias = "M", authCodeString = "payment")
    Page<Payment> selectItems(Page<Payment> page, @Param(Constants.WRAPPER) QueryWrapper<Payment> wrapper);

    /**
     * 根据id查详情
     * @param Id
     * @return
     */
    @Select("select M.* from (select a.*,aa.username as adminName, ad.name as departmentName from dragon_payment a  " +
            "LEFT JOIN dragon_admin aa on a.admin_id  = aa.id " +
            "LEFT JOIN dragon_department ad on a.department_id = ad.id) M where 1=1 and M.id = #{id} ")
    Payment selectDetailById(Integer Id);

    /**
     * 付款 月
     * @return
     */
    @Select("SELECT SUM(a.money) FROM dragon_payment a WHERE a.status = 1 and LEFT(a.create_time,7) = LEFT(NOW(),7)")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "payment")
    Double selectMonthPaymentMoney();

    /**
     * 付款 总
     * @return
     */
    @Select("SELECT SUM(a.money) FROM dragon_payment a WHERE a.status = 1 and deleted=0 and payment_status = 1 ")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "payment")
    Double selectYearPaymentMoney();

    /**
     * 付款 年金额
     * @param year
     * @return
     */
    @Select("SELECT SUM(a.money) FROM dragon_payment a WHERE a.status = 1 and deleted=0 and payment_status = 1 AND LEFT(a.create_time,4) = #{year} ")
    @DataScope(adminAlias = "a", adminField = "admin_id", deptAlias = "a", deptField = "department_id", authCodeString = "payment")
    Double selectMoneyForYear(String year);


    @Select("with base as(" +
            "    select money,create_time from dragon_payment where status = 1 and deleted = 0 and payment_status = 1" +
            ")," +
            "totalMoney as (" +
            "    select sum(money) totalRemitMoney from base" +
            ")," +
            "yearMoney as (" +
            "    select sum(money) yearRemitMoney from base where left(create_time,4) = left(now(),4)" +
            ")," +
            "monthMoney as (" +
            "    select sum(money) monthRemitMoney from base where left(create_time,7) = left(now(),7)" +
            ")," +
            "makeData as (" +
            "    select a.totalRemitMoney,b.yearRemitMoney,c.monthRemitMoney from totalMoney a,yearMoney b,monthMoney c" +
            ")" +
            "select * from makeData")
    Map<String,Object> selectListStatistics();

    /**
     * 付款类型占比
     * @param time
     * @return
     */
    @Select("with base as (select id,money,type,payment_status,create_time from dragon_payment " +
            "where status = 1 and deleted = 0 and payment_status = 1 and YEAR(create_time) = #{time})," +
            "totalBase as ( select sum(money) as totalMoney from base ), " +
            "typeBase as ( select a.gongyingshang, b.gongzi, c.richang, d.qita from " +
            " (select sum(CASE WHEN type = 1 THEN ifnull(money,0) ELSE 0 END ) as gongyingshang from base) a, " +
            " ( select sum(CASE WHEN type = 2 THEN ifnull(money,0) ELSE 0 END ) as gongzi from base  ) b, " +
            " ( select sum(CASE WHEN type = 3 THEN ifnull(money,0) ELSE 0 END ) as richang from base  ) c, " +
            " ( select sum(CASE WHEN type = 4 THEN ifnull(money,0) ELSE 0 END ) as qita from base  ) d " +
            "), " +
            "averageBase as ( " +
            "select " +
            "  IFNULL( ROUND( (a1.gongyingshang / a.totalMoney )*100,1) ,0)  as '供应商', " +
            "  IFNULL( ROUND( (a1.gongzi / a.totalMoney )*100,1) ,0)  as '工资', " +
            "  IFNULL( ROUND( (a1.richang / a.totalMoney )*100,1) ,0)  as '日常', " +
            "  IFNULL( ROUND( (a1.qita / a.totalMoney )*100,1) ,0)  as '其他'  " +
            "from totalBase a, typeBase a1 " +
            ") " +
            "select * from averageBase")
    Map<String, Object>  paymentTypePercentage(String time);

    /**
     * 付款月总额统计
     * @param time
     * @return
     */
    @Select("SELECT  " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '1' THEN money ELSE 0 END ),0) AS Jan, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '2' THEN money ELSE 0 END ),0) AS Feb, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '3' THEN money ELSE 0 END ),0) AS Mar, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '4' THEN money ELSE 0 END ),0) AS Apr, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '5' THEN money ELSE 0 END ),0) AS May, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '6' THEN money ELSE 0 END ),0) AS June, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '7' THEN money ELSE 0 END ),0) AS July, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '8' THEN money ELSE 0 END ),0) AS Aug, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '9' THEN money ELSE 0 END ),0) AS Sept, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '10' THEN money ELSE 0 END ),0) AS Oct, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '11' THEN money ELSE 0 END ),0) AS Nov, " +
            " IFNULL(sum( CASE MONTH ( create_time ) WHEN '12' THEN money ELSE 0 END ),0) AS Dece " +
            "FROM dragon_payment  " +
            "WHERE status = 1 and deleted = 0 and payment_status = 1 and YEAR(create_time) = #{time} ")
    LinkedHashMap<String, Object> paymentTotalStatistics(String time);
}
